﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Common;
using System.Text;
using StoreManagementSystem.DomainObjects;
using System.Data;


namespace StoreManagementSystem.DataAccessLayer
{
    public class SQLStoreUserDAO : IDataAccessObject<Entity>
    {
        private ISQLDataProvider DataProvider { get; set; }
        public SQLStoreUserDAO(ISQLDataProvider SQLDataProvider) 
        {
            DataProvider = SQLDataProvider;
        }

        /// <summary>
        /// Registers a user to the system using parameterized queries
        /// </summary>
        /// <param name="e"></param>
        public void create(Entity e)
        {
            if (e is StoreUser)
            {
                StoreUser user = (StoreUser) e;
                List<SQLParam> dbparams = new List<SQLParam>();
                dbparams.Add(new SQLParam("?id", user.id));
                dbparams.Add(new SQLParam("?FirstName", user.FirstName));
                dbparams.Add(new SQLParam("?LastName", user.LastName));
                dbparams.Add(new SQLParam("?Password", user.Password));
                dbparams.Add(new SQLParam("?LoginName", user.LoginName));
                dbparams.Add(new SQLParam("?Email", user.Email));

                // build the parameter string to pass in values
                StringBuilder paramstr = new StringBuilder();
                for (int i = 0; i < dbparams.Count - 1; i++)
                {
                    paramstr.Append(dbparams[i].Name + ",");
                }
                paramstr.Append(dbparams[dbparams.Count - 1].Name);

                string SQL = "INSERT INTO storeuser (id,FirstName,LastName,Password,LoginName,Email) " +
                        "VALUES(" + paramstr.ToString() + ");";
                DataProvider.Open();
                DataProvider.ExecuteSQL(SQL, dbparams, false);
                DataProvider.Close();
            }
        }

 

        Entity IDataAccessObject<Entity>.get(params string[] parameters)
        {
            List<SQLParam> dbparams = new List<SQLParam>();
            string SQL;
            string FirstName = "";
            string LastName = "";
            string LoginName = "";
            string Email = "";
            switch (parameters.Length)
            {
                case 1:
                    FirstName = parameters[0];
                    dbparams.Add(new SQLParam("?FirstName", FirstName));
                    SQL = "SELECT * FROM storeuser WHERE FirstName=?FirstName";
                    break;
                case 2:
                    FirstName = parameters[0];
                    LastName = parameters[1];
                    dbparams.Add(new SQLParam("?FirstName", FirstName));
                    dbparams.Add(new SQLParam("?LastName", LastName));
                    SQL = "SELECT * FROM storeuser WHERE FirstName=?FirstName AND LastName=?LastName";
                    break;
                case 3:
                    FirstName = parameters[0];
                    LastName = parameters[1];
                    LoginName = parameters[2];
                    dbparams.Add(new SQLParam("?FirstName", FirstName));
                    dbparams.Add(new SQLParam("?LastName", LastName));
                    dbparams.Add(new SQLParam("?LoginName", LoginName));
                    SQL = "SELECT * FROM storeuser WHERE FirstName=?FirstName AND LastName=?LastName AND LoginName=?LoginName";
                    break;
                case 4:
                    FirstName = parameters[0];
                    LastName = parameters[1];
                    LoginName = parameters[2];
                    Email = parameters[3];
                    dbparams.Add(new SQLParam("?FirstName", FirstName));
                    dbparams.Add(new SQLParam("?LastName", LastName));
                    dbparams.Add(new SQLParam("?LoginName", LoginName));
                    dbparams.Add(new SQLParam("?Email", Email));
                    SQL = "SELECT * FROM storeuser WHERE (FirstName=?FirstName) AND (LastName=?LastName) AND (LoginName=?LoginName) AND (Email=?Email)";
                    break;
                default:
                    throw new Exception("Invalid number of parameters!");
            }
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            if (dt.Rows.Count == 0)
                return null;
            DataProvider.Close();
            StoreUser user = new StoreUser();
            user.id = dt.Rows[0]["id"].ToString();
            user.FirstName = dt.Rows[0]["FirstName"].ToString();
            user.LastName = dt.Rows[0]["LastName"].ToString();
            user.Password = dt.Rows[0]["Password"].ToString();
            user.LoginName = dt.Rows[0]["LoginName"].ToString();
            user.Email = dt.Rows[0]["Email"].ToString();
            return user;
        }

        public StoreUser getUser(string LoginName, string Password)
        {
            List<SQLParam> dbparams = new List<SQLParam>();
            dbparams.Add(new SQLParam("?LoginName", LoginName));
            dbparams.Add(new SQLParam("?Password", Password));
            string SQL = "SELECT * FROM storeuser WHERE (LoginName=?LoginName) AND (Password=?Password)";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            DataProvider.Close();
            if (dt.Rows.Count == 0)
                return null;
            StoreUser user = new StoreUser();
            user.id = dt.Rows[0]["id"].ToString();
            user.FirstName = dt.Rows[0]["FirstName"].ToString();
            user.LastName = dt.Rows[0]["LastName"].ToString();
            user.Password = dt.Rows[0]["Password"].ToString();
            user.LoginName = dt.Rows[0]["LoginName"].ToString();
            user.Email = dt.Rows[0]["Email"].ToString();
            return user;
        }

        public void remove(Entity e)
        {
            throw new NotImplementedException();
        }

        public void update(Entity e)
        {
            throw new NotImplementedException();
        }
    }
}